# -*- coding: utf-8 -*-
from re import match
import psycopg2,logging


def escape(text): return str(text).replace('\\','\\\\').replace("'","\\'")


def head_log(text):
    return '<div style="font: 13px Tahoma"><b>%s</b></div>'%text

def text_log(text):
    return '<div style="margin-left:10px; font: 12px Tahoma">%s</div>'%text

def adv_log(text):
    return '<div style="margin-left:10px; font: 11px Tahoma;color: gray; margin-bottom:3px;">%s</div>'%text

class Psql:
    def __init__(self,config,debug=True):
        self.sql_objects={}
        self.loging=[]
        self.log=self.loging.append
        self.config=config.DB_CONF

        self.connection=psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'"%(self.config['db'],self.config['user'],self.config['host'],self.config['pass']))
        self.query=self.connection.cursor()

        self.sequence_list=[]


        self.log(head_log('Connection established'))

        self.debug=debug
        if self.debug:
            self.prepare_sequence()
            self.table_list=self.prepare_table_list()
            self.index_list=self.get_index_list()


    def get_table_info(self,table_name):


        try:
            self.log(adv_log('Getting table info <i>"%s"</i>'%table_name))

            self.query.execute("""        
            SELECT column_name as name,
                column_default as "default", 
                is_nullable as "null", 
                udt_name as "type",
                character_maximum_length as "max"
                  FROM information_schema.columns                 
                     WHERE table_name = '%s';                
            """%table_name)



        except psycopg2.InternalError:
            self.connection._rollback()

        rows=self.query.fetchall()
        info={}

        for name,default,notnull,type,max in rows:
            if isinstance(default,bool):
                default=='true' if default else 'false'

            info[name]={'type' : type,'default': default,'notnull' : True if notnull=='NO' else None,'max' : max }

        self.log(adv_log('Got info for %s (%s)<br>'%(table_name,str(info))))

        return info

    def get_constraint_info(self,table_name):
        """ table_name - имя таблицы
            возвращает список из имен
            constraint (forign) ключей"""
        try:
            self.query.execute("SELECT constraint_name \n\
                                FROM information_schema.table_constraints \n\
                                WHERE table_name = '%s' AND constraint_type='FOREIGN KEY'; "%table_name)
        except psycopg2.InternalError:
            self.connection._rollback()
        rows=self.query.fetchall()
        #logging.debug( rows )
        info=[]
        for i in rows:
            info.append(i[0])
        info=tuple(info)
        #logging.debug( info )
        return info

    def prepare_sequence(self):

        self.log(head_log('Getting sequence list'))
        self.query.execute("""
        
        SELECT relname, relowner
        FROM pg_class
        WHERE relkind = 'S'
           AND relnamespace IN (
                SELECT oid
                  FROM pg_namespace
                 WHERE nspname NOT LIKE 'pg_%'
                   AND nspname != 'information_schema'              
        );
        
        """)
        data=self.query.fetchall()
        if data:
            for seq_name,owner in data:
                if seq_name not in self.sequence_list:
                    self.sequence_list.append(seq_name)
        self.log(adv_log('Got sequences: <br>%s<br>'%str(self.sequence_list)))


    def prepare_table_list(self):
        self.log(head_log('Getting table list'))
        self.query.execute("""
        
        SELECT table_name, table_schema
          FROM information_schema.tables
             WHERE table_type = 'BASE TABLE'
              AND table_schema NOT IN
              ('pg_catalog', 'information_schema');             
                
                        
        """)
        table_list=[]
        data=self.query.fetchall()
        if data:
            for table_name,table_schema in data:
                if table_name not in table_list:
                    table_list.append(table_name)

        self.log(adv_log('Got tables: %s'%str(table_list)))
        return table_list

    def get_index_list(self):
        self.query.execute("""        
        SELECT relname, indkey
          FROM pg_class, pg_index
         WHERE pg_class.oid = pg_index.indexrelid
           AND pg_class.oid IN (
            SELECT indexrelid
              FROM pg_index, pg_class     
               WHERE pg_class.oid=pg_index.indrelid
               AND indisunique != 't'
               AND indisprimary != 't'
        );        
        """)
        data=self.query.fetchall()
        index_list=[]
        if data:
            for relname,indkey in data:
                if relname not in index_list:
                    index_list.append(relname)


        return index_list


    def parse_column(self,table_name,data):
        name,type,max,default,primary_key,sequence,autoSequence,notnull,autoIndex=data

        result=[]
        result.append('"%s"'%escape(name))

        result.append('%s(%d)'%(type,max) if max else '%s'%type)
        pr_key=''
        if primary_key:
            pr_key=name

        if isinstance(notnull,bool):
            if notnull:
                result.append('NOT NULL')
            else:
                notnull=None

        if not autoSequence:

            if type=='timestamp' or type=='date':

                if default=='now':
                    default="('now'::text)::timestamp without time zone"
                    result.append("DEFAULT %s"%default)

            else:

                if isinstance(default,bool):
                    if type=='bool':

                        if default:

                            default='true'

                        else:

                            default='false'


                        result.append('DEFAULT %s'%default)

                else:
                    if default:
                        if isinstance(default,int):


                            result.append('DEFAULT %s'%default)

                        if isinstance(default,str):

                            default="'%s'::character varying"%escape(default)

                            result.append("DEFAULT %s"%default)
        else:

            if type in ['int2','int4','int8']:

                iNewSequenceName=table_name+'_'+name+'_sequence'

                default="nextval('%s'::regclass)"%iNewSequenceName

                result.append("DEFAULT %s"%default)

                '''
                Creating Sequence
                '''

                self.sequence_create_try(iNewSequenceName)

        if isinstance(autoIndex,bool):
            iNewIndexName=table_name+'_'+name+'_index'
            self.index_create_try(table_name,name,iNewIndexName)


        info={'type' : type,'default': default,'notnull' : notnull,'max' : max }

        return result,pr_key,info

    def register(self,table_name,object):
        self.log(head_log('Registering table: <span style="color:blue">%s</span><br>'%str(table_name)))
        self.sql_objects[ table_name ]={}
        if self.debug:
            pr_key=False
            all=[]
            current={}
            for data in object:
                res,primary_key,current_data=self.parse_column(table_name,data)

                current[ data[0] ]=(current_data,res)

                if primary_key:
                    pr_key=primary_key

                all.append(' '.join(res))

            if pr_key:
                all.append('PRIMARY KEY ("%s")'%pr_key)


            '''
            Creating table
            '''
            self.create_table_try(table_name,",\n".join(all),current)



    def create_table_try(self,table_name,columns,current_data):



        sql=['CREATE TABLE "public"."%s"('%table_name]
        sql.append(columns)
        sql.append(' )  WITHOUT OIDS;')
        #logging.debug(' '.join(sql))
        #sdf
        if table_name not in self.table_list:
            self.log(head_log('Creating table: <span style="color:red">%s</span><br>'%str(table_name)))
            logging.debug(' '.join(sql))
            self.query.execute(' '.join(sql))
            self.commit()
        else:
            sqlInfo=self.get_table_info(table_name)
            newInfo=current_data

            for column,info in newInfo.items():
                if column not in sqlInfo:
                    alter_info=' '.join(info[1])
                    '''
                    Adding new column
                    '''
                    self.alter_table_set_new_column(table_name,alter_info)

                else:
                    rebuildColumn=False
                    meta_new_info=info[0]
                    meta_sql_info=sqlInfo[column]

                    for meta_key,meta_value in meta_sql_info.items():

                        if str(meta_value)!=str(meta_new_info[meta_key]):

                            rebuildColumn=True

                    if rebuildColumn:
                        self.alter_table_set_new_meta(table_name,column,meta_new_info)


    def forein_key(self,table_name,column ,forein_table,forein_column,update='no action',delete='no action'):
        """ ф-я создает внешний ключ (forign key)
            с именем table_name_column_fn
        
            table_name - имя таблицы
            column - имя колонки для которой создаетс я внешний ключ
            forein_table - имя связаной таблицы
            forein_column - имя связаной колонки
            update - действие при обновлении данных в forein_column
            delete - действие при удалении данных в forein_column
            действия могут быть:
            'no action', 'cascade', 'restrict', 'set null', 'set default'"""

        fk=self.get_constraint_info(table_name)
        #logging.debug( fk )
        constraint='%s_%s_fk'%(table_name,column)
        if constraint not in fk:
            patern="ALTER TABLE %s \n\
                    ADD CONSTRAINT \"%s\" FOREIGN KEY(\"%s\") \n\
                    REFERENCES %s(\"%s\") \n\
                    on update %s \n\
                    on delete %s ;"%(table_name,constraint,column,
                                       forein_table,forein_column,
                                       update,delete)
            #logging.debug( patern )
            self.query.execute(patern)
            self.commit()
    def alter_table_set_new_meta(self,table,column,meta):



        pattern="ALTER TABLE %s  ALTER column \"%s\" "%(table,column)
        alterSyntax=[]; write=alterSyntax.append

        if meta['default']:
            if isinstance(meta['default'],int):
                write(pattern+'SET DEFAULT %d'%meta['default'])
            else:
                write(pattern+'SET DEFAULT %s'%meta['default'])

        else:
            write(pattern+'DROP DEFAULT')

        if meta['max']:
            type='%s(%d)'%(meta['type'],meta['max'])
        else:
            type=meta['type']
        write(pattern+"TYPE %s"%type)

        if isinstance(meta['notnull'],bool):
            if meta['notnull']:
                write(pattern+'SET NOT NULL')
            else:
                write(pattern+'DROP NOT NULL')
        else:
            write(pattern+'DROP NOT NULL')


        self.log(head_log('Altering table: <span style="color:red">%s</span><div>%s</div><br>'%(str(table),";".join(alterSyntax))))
        self.query.execute(";".join(alterSyntax))
        self.commit()


    def commit(self):
        self.connection.commit()

    def alter_table_set_new_column(self,table_name,meta):
        self.log(head_log('Altering table: <span style="color:red">%s</span><br>'%"ALTER TABLE public.%s ADD COLUMN %s"%(table_name,meta)))

        self.query.execute("ALTER TABLE public.%s ADD COLUMN %s"%(table_name,meta))
        self.commit()

    def sequence_create_try(self,sequence_name):
        if sequence_name not in self.sequence_list:
           self.log(head_log('Creating sequence: <span style="color:red">%s</span><br>'%sequence_name))
           self.query.execute('CREATE SEQUENCE %s START 1;'%sequence_name)
           self.commit()

    def index_create_try(self,table,column,index_name):
        if table in self.table_list:
            if index_name not in self.index_list:
                self.log(head_log('Creating index: <span style="color:red">%s</span><br>'%index_name))
                self.query.execute('CREATE INDEX %s ON %s(%s);'%(index_name,table,column))
                self.commit()

    """
    
    Functions to work with sql
    
    """

    def insert(self,table ,values,debug=False):
        '''   
         
        Collecting sql query for insert. Escaping string. Wraps'em if its needed
        
        sql_insert('user',{'login': 'admin' })    
        
        '''
        #Query start building
        q=[ "INSERT INTO \"%s\" ("%escape(table).replace('"','') ]; rows=[]; val=[]
        #Parsing data
        for row,data in values.items():
            r=row.split("."); row_formed='"%s"."%s"'%(r[0],r[1]) if len(r)==2 else '"%s"'%row
            rows.append(row_formed)
            if isinstance(data,list) and len(data)==1:
                if isinstance(data[0],str):

                    iAppend=data[0]
                else:
                    raise Exception('Data Error %s'%data[0])
            else:
                #if isinstance(data, str) or isinstance(data, int):            
                if match('^\d{1,11}$',str(data)):
                    iAppend=str(data)
                else:
                    iAppend="'%s'"%escape(data) if data else "''"

            val.append(iAppend)



        q.append(','.join(rows)); q.append(') VALUES ('); q.append(','.join(val)); q.append(');')
        queryString=''.join(q)
        try:
            if not debug:
                self.query.execute(queryString)
                #connection.commit()
                return queryString
            else:
                return queryString

        except psycopg2.InternalError:

            self.connection._rollback()


    def update(self,table,values,advanced=None,debug=False):

        """    
        Collecting sql query for update. Escaping string. Wraps'em if it's needed
        
        sql_update('user',{'login': 'admin' }, 'WHERE id = 3')    
        
        """

        q=[ "UPDATE \"%s\" SET "%table]; val=[]

        for row,data in values.items():
            r=row.split(".");

            row_formed='"%s"."%s"'%(r[0],r[1]) if len(r)==2 else '"%s"'%row



            if isinstance(data,list) and len(data)==1:
                if isinstance(data[0],str):

                    iAppend=data[0]
                else:
                    raise Exception('Data error %s'%data[0])
            else:
                #if isinstance(data, str) or isinstance(data, int):            
                if match('^\d{1,11}$',str(data)):
                    iAppend=str(data)
                else:
                    iAppend="'%s'"%escape(data) if data else "''"


            val.append(" %s = %s"%(row_formed,iAppend))

        q.append(','.join(val))


        q.append(' '+advanced) if advanced else ''

        queryString=''.join(q)

        try:
            if not debug:
                self.query.execute(queryString)
            else:
                return queryString

        except psycopg2.InternalError:
            self.connection._rollback()


    def delete(self,table,data):
        sql=['DELETE FROM %s WHERE'%str(table)]
        And=[]
        for k,v in data.items():
            And.append("%s=%s"%(k,str(v) if match('^\d{1,11}$',str(data)) else "'%s'"%escape(v)))

        sql.append(' '+' AND '.join(And))
        self.query.execute(' '.join(sql))

    def seq(self,name):
        self.query.execute("SELECT nextval('%s'::regclass)"%name)
        rows=self.query.fetchone()
        if rows:
            return rows[0]

    def select(self,q ,args=False,fetchone=False,fetchall=False,count=False,debug=False):
        #logging.debug('first---   '+q)
        #logging.debug(args)
        if args:
            if isinstance(args,str) or isinstance(args,long) or isinstance(args,int):
                args=(args,)
            #logging.debug('second---   '+q)
            q=self.query.mogrify(q,args)
            #logging.debug('third---   '+q)
        try:
            if not debug:
                self.query.execute(q)
            else:
                return q
        except psycopg2.InternalError:
            self.connection._rollback()

        if fetchone:
            return self.query.fetchone()
        if fetchall:
            return self.query.fetchall()
        if count:
            return self.query.rowcount


        return self.query.fetchall()



    def get_log(self):

        return ''.join(self.loging)


